﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using ANZ_AIM_DAL;
using System.Globalization;
using ANZ_AIM_BLL.Model;
using ANZ_AIM_BLL.Utils;
using ANZ_AIM_FRAMEWORK;

namespace ANZ_AIM_BLL
{
    public class SaleChangeRequestBLL
    {
        private string ConnectionString;
        private const string TABLE_NAME = "SaleChangeRequest";
        private Dictionary<string, string> ObjectPropDict;

        public SaleChangeRequestBLL()
        {
            ConnectionString = DBHelper.GetMainMdbConnectionString();
            this.ObjectPropDict = BuildObjectPropertyMapping();
        }

        public void Save(List<SaleChangeRequest> saleChangeRequestList)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                OleDbParameter[] parameters;
                string command = string.Empty;
                foreach (SaleChangeRequest saleChange in saleChangeRequestList)
                {
                    command = CommandHelper.BuildInsertCommand(saleChange, dict, out parameters);
                    OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
                }
            }
            catch (Exception ex) { throw ex; }
        }

        public DataTable GetByBatchFileChangeID(string batchFileChangeID)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                SaleChangeRequest saleChangeRequest = new SaleChangeRequest() { BatchFileChangeID = batchFileChangeID };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileChangeID", "BatchFileChangeID"}
                    };
                string command = CommandHelper.BuildSelectCommand(saleChangeRequest, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);

                return dsResult.Tables[0];
            }
            catch (Exception ex) { throw ex; }
        }

        public string[] NewValByBatchFileProfID(string batchFileChangeID, string profileId, string fieldChange)
        {
            try
            {
                var dict = MiscHelper.Clone(this.ObjectPropDict);
                SaleChangeRequest saleChangeRequest = new SaleChangeRequest() { FieldChange = fieldChange, BatchFileChangeID = batchFileChangeID, ProfileID = profileId };
                OleDbParameter[] parameters;
                var conditionDict = new Dictionary<string, string>
                    {
                        {"BatchFileChangeID", "BatchFileChangeID"},
                        {"ProfileID", "ProfileID"},
                        {"FieldChange", "FieldChange"}
                    };
                string command = CommandHelper.BuildSelectCommand(saleChangeRequest, dict, conditionDict, out parameters);
                DataSet dsResult = OleDbHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, command, parameters);
                DataRow dtRow = dsResult.Tables[0].Rows[0];

                if (!Employment_Field().Contains(dtRow["FieldChange"].ToString().Replace(" ", string.Empty).Replace("_", string.Empty).Replace("/", string.Empty))) {
                    return null;
                }
                else
                {
                    return new string[] { dtRow["FieldChange"].ToString().Replace(" ", string.Empty).Replace("_", string.Empty).Replace("/", string.Empty), dtRow["NewValue"].ToString() };
                }
                
            }
            catch (Exception ex) { throw ex; }
        }

        public void Delete(string batchFileChangeID)
        {
            try
            {
                var dict = new Dictionary<string, string>
                    {
                        {"BatchFileChangeID", "BatchFileChangeID"}
                    };
                SaleChangeRequest saleChangeRequest = new SaleChangeRequest() { BatchFileChangeID = batchFileChangeID };
                OleDbParameter[] parameters;
                string command = string.Empty;
                command = CommandHelper.BuildDeleteCommand(saleChangeRequest, dict, out parameters);
                int n = OleDbHelper.ExecuteNonQuery(this.ConnectionString, CommandType.Text, command, parameters);
            }
            catch (Exception ex) { throw ex; }
        }
        public Dictionary<string, string> BuildObjectPropertyMapping()
        {
            Dictionary<string, string> dictionary = new Dictionary<string, string>
            {
                {"ProfileID", "ProfileID"},
                {"BatchFile", "BatchFile"},
                {"FieldChange", "FieldChange"},
                {"OldValue", "OldValue"},
                {"NewValue", "NewValue"},
                {"BatchFileChangeID", "BatchFileChangeID"}
            };

            return dictionary;
        }

        public List<string> Employment_Field()
        {
            List<string> employment_FieldList = new List<string>();
            employment_FieldList.Add("ProfileID");
            employment_FieldList.Add("SaleCode");
            employment_FieldList.Add("PersonalCode");
            employment_FieldList.Add("EmployeeFullName");
            employment_FieldList.Add("EmployeeFullNameE");
            employment_FieldList.Add("Role");
            employment_FieldList.Add("Level");
            employment_FieldList.Add("Category");
            employment_FieldList.Add("ProductCategory");
            employment_FieldList.Add("Product");
            employment_FieldList.Add("TeamName");
            employment_FieldList.Add("TeamLeaderCode");
            employment_FieldList.Add("Channel");
            employment_FieldList.Add("JoiningDate");
            employment_FieldList.Add("PromotionTransferDate");
            employment_FieldList.Add("Status");
            employment_FieldList.Add("ContractType");
            employment_FieldList.Add("ContractTerm");
            employment_FieldList.Add("ContractorName");
            employment_FieldList.Add("ContractSignDate");
            employment_FieldList.Add("ContractExpiryDate");
            employment_FieldList.Add("BatchFileID");
            return employment_FieldList;
        }
       
    }
}
